package com.cs.beneficios.business.entity;

import com.cs.beneficios.business.model.AreaVO;
import com.cs.beneficios.common.ConnectionFactory;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;

public class AreasDAO {
	/**
	 * Conexion a la base de datos.
	 */
	private Connection conn = null;
	private ConnectionFactory cf;

	/**
	 * Constructor que inicializa la conexion con la base de datos.
	 * 
	 * @param cf
	 *            Conexion con la base de datos...
	 */
	public AreasDAO(ConnectionFactory cf) {
		this.cf = cf;
		try {
			this.conn = this.cf.getConnection();
		} catch (SQLException e) {
			System.err.println("/******** TDSUB - cwsub");
			e.printStackTrace();
			System.err.println("TDSUB - cwsub ********/");
		}
	}

	public List ViewAreas(String strNombreArea) throws SQLException {
		String strSQL = "SELECT CODIGO, NOMBRE, DESCRIPCION, NVL(AREA_CODIGO,'') AREA_CODIGO "
				+ "FROM TCS_BEN_AREA " + "WHERE AREA_CODIGO IS NULL ";

		if ((Object) strNombreArea != null) {
			strSQL += " AND NOMBRE LIKE UPPER('%" + strNombreArea + "%') ";
		}
		strSQL += "ORDER BY NOMBRE";

		List lListaAreas = new LinkedList();
		ResultSet rs = null;
		try {
			rs = cf.getRecordsRset(strSQL, null, conn);
			while (rs.next()) {
				AreaVO objAreaVO = new AreaVO();
				objAreaVO.setCode(new Long(rs.getString("CODIGO")));
				objAreaVO.setCodearea(rs.getString("AREA_CODIGO"));
				objAreaVO.setDescripcion(rs.getString("DESCRIPCION"));
				objAreaVO.setNombre(rs.getString("NOMBRE"));
				lListaAreas.add(objAreaVO);
				objAreaVO = null;
			}
		} finally {
			if (rs != null)
				rs.close();
		}

		return lListaAreas;
	}

	public List ViewAllAreas() throws SQLException {
		String strSQL = "SELECT CODIGO, NOMBRE, DESCRIPCION, NVL(AREA_CODIGO,'') AREA_CODIGO "
				+ "FROM TCS_BEN_AREA "
				+ "WHERE AREA_CODIGO IS NULL "
				+ "ORDER BY NOMBRE";

		List lListaAreas = new LinkedList();
		ResultSet rs = null;
		try {
			rs = cf.getRecordsRset(strSQL, null, conn);
			while (rs.next()) {
				AreaVO objAreaVO = new AreaVO();
				objAreaVO.setCode(new Long(rs.getString("CODIGO")));
				objAreaVO.setCodearea(rs.getString("AREA_CODIGO"));
				objAreaVO.setDescripcion(rs.getString("DESCRIPCION"));
				objAreaVO.setNombre(rs.getString("NOMBRE"));
				lListaAreas.add(objAreaVO);
				objAreaVO = null;
			}
		} finally {
			if (rs != null)
				rs.close();
		}

		return lListaAreas;
	}

	public List SearchArea(String strCode) throws SQLException {
		String strSQL = "SELECT CODIGO, NOMBRE, DESCRIPCION, NVL(AREA_CODIGO,'') AREA_CODIGO "
				+ "FROM TCS_BEN_AREA " + "WHERE CODIGO =" + strCode + " ";

		List lListaAreas = new LinkedList();
		ResultSet rs = null;
		try {
			rs = cf.getRecordsRset(strSQL, null, conn);
			if (rs.next()) {
				AreaVO objAreaVO = new AreaVO();
				objAreaVO.setCode(new Long(rs.getString("CODIGO")));
				objAreaVO.setCodearea(rs.getString("AREA_CODIGO"));
				objAreaVO.setDescripcion(rs.getString("DESCRIPCION"));
				objAreaVO.setNombre(rs.getString("NOMBRE"));
				lListaAreas.add(objAreaVO);
				objAreaVO = null;
			}
		} finally {
			if (rs != null)
				rs.close();
		}

		return lListaAreas;
	}

	public int InsertArea(String[] Arr) throws SQLException {
		String query = "INSERT INTO TCS_BEN_AREA("
				+ "CODIGO, NOMBRE, DESCRIPCION, AREA_CODIGO, VIGENCIA, usuario_crea, fecha_crea, terminal_crea, "
				+ "usuario_modifica, fecha_modifica, terminal_modifica) "
				+ "VALUES(SCS_BEN_AREA.NextVal, upper(?), upper(?), ?, 'A', ?, to_date(?,'dd/mm/yyyy'), ?, "
				+ "?, to_date(?,'dd/mm/yyyy'), ?)";

		return cf.execSql(query, Arr, conn);
	}

	public int UpdateArea(String[] Arr, String strCode) throws SQLException {
		String strSql = "UPDATE TCS_BEN_AREA SET " + "NOMBRE = UPPER(?), "
				+ "DESCRIPCION = UPPER(?), " + "usuario_modifica = ?, "
				+ "fecha_modifica = to_date(?,'dd/mm/yyyy'), "
				+ "terminal_modifica = ? " + "WHERE CODIGO=" + strCode + " ";

		return cf.execSql(strSql, Arr, conn);
	}

	public int UpdateSubArea(String[] Arr, String strCode) throws SQLException {
		String strSql = " UPDATE TCS_BEN_AREA SET " + " NOMBRE = UPPER(?), "
				+ " DESCRIPCION = UPPER(?), " + " AREA_CODIGO = ?, "
				+ " usuario_modifica = ?, "
				+ " fecha_modifica = to_date(?,'dd/mm/yyyy'), "
				+ " terminal_modifica = ? " + " WHERE CODIGO=" + strCode + " ";

		return cf.execSql(strSql, Arr, conn);
	}

	public List ViewAllSubAreas(String strCodeArea) throws SQLException {
		String strSQL = "SELECT CODIGO, NOMBRE, DESCRIPCION, NVL(AREA_CODIGO,'') AREA_CODIGO, "
				+ "(SELECT NOMBRE FROM TCS_BEN_AREA WHERE CODIGO ="
				+ strCodeArea
				+ ") AREA "
				+ "FROM TCS_BEN_AREA "
				+ "WHERE AREA_CODIGO = "
				+ strCodeArea
				+ " "
				+ "ORDER BY NOMBRE";

		List lListaAllSubAreas = new LinkedList();
		ResultSet rs = null;
		try {
			rs = cf.getRecordsRset(strSQL, null, conn);
			while (rs.next()) {
				AreaVO objAreaVO = new AreaVO();
				objAreaVO.setCodearea(rs.getString("AREA_CODIGO"));
				objAreaVO.setDescripcion(rs.getString("DESCRIPCION"));
				objAreaVO.setNombre(rs.getString("AREA"));
				objAreaVO.setNombresubarea(rs.getString("NOMBRE"));
				objAreaVO.setCode(new Long(rs.getString("CODIGO")));
				lListaAllSubAreas.add(objAreaVO);
				objAreaVO = null;
			}
		} finally {
			if (rs != null)
				rs.close();
		}

		return lListaAllSubAreas;
	}

	public List ViewAllSubSecre(String strCodeArea) throws SQLException {
		String strSQL = "SELECT CODIGO,NOMBRE,DESCRIPCION,NVL(AREA_CODIGO,'') AREA_CODIGO, "
				+ "(SELECT NOMBRE FROM TCS_BEN_AREA WHERE CODIGO="
				+ strCodeArea
				+ ") AREA "
				+ "FROM TCS_BEN_AREA "
				+ "WHERE AREA_CODIGO = "
				+ strCodeArea
				+ " "
				+ "ORDER BY NOMBRE";

		List lListaAllSubAreas = new LinkedList();
		ResultSet rs = null;
		try {
			rs = cf.getRecordsRset(strSQL, null, conn);
			while (rs.next()) {
				AreaVO objAreaVO = new AreaVO();
				objAreaVO.setCodearea(rs.getString("CODIGO"));
				objAreaVO.setNombresubarea(rs.getString("NOMBRE"));
				lListaAllSubAreas.add(objAreaVO);
				objAreaVO = null;
			}
		} finally {
			if (rs != null)
				rs.close();
		}

		return lListaAllSubAreas;
	}

	public Collection getAreasAuthorized(String strCodeUsu, String strGroupUser)
			throws SQLException {
		String strSQL = null;
		if (strGroupUser.equals("ADMINISTRADOR GENERAL")
				|| strGroupUser.equals("SUPER USUARIO")) {
			strSQL = "SELECT distinct area.CODIGO CODIGO, area.NOMBRE NOMBRE "
					+ "FROM tcs_ben_area area "
					+ "WHERE area.AREA_CODIGO is null "
					+ "ORDER BY area.NOMBRE ";
		} else {
			/*
			 * strSQL = "SELECT area1.CODIGO CODIGO, area1.NOMBRE NOMBRE "+
			 * "from TCS_BEN_AREA area1 " +
			 * "where area1.CODIGO in (select distinct area.AREA_CODIGO "+
			 * "                       from tcs_ben_subprograma_usuario subus, "
			 * +
			 * "                       tcs_ben_subprograma sub, tcs_ben_area area "
			 * + "                       where subus.SBPR_CODIGO = sub.CODIGO "
			 * + "                       and area.CODIGO = sub.AREA_CODIGO " +
			 * "                       and subus.USU_CODIGO = "
			 * +strCodeUsu+" ) "+ "order by area1.NOMBRE ";
			 */

			strSQL = "select a.codigo codigo, a.nombre nombre "
					+ "from tcs_ben_area a "
					+ "where a.codigo = any(select pro.area_codigo "
					+ "from TCS_BEN_PROGRAMA pro "
					+ "where (pro.vigencia = 'A' OR sysdate between pro.DESDE_VIGENCIA and  pro.HASTA_VIGENCIA)  "
					+ "and pro.usu_codigo = "
					+ strCodeUsu
					+ " "
					+ "union select area.area_codigo "
					+ "from tcs_ben_subprograma sub, tcs_ben_area area "
					+ "where sub.area_codigo = area.codigo "
					+ "and (sub.vigencia = 'A' OR sysdate between sub.DESDE_VIGENCIA and  sub.HASTA_VIGENCIA) "
					+ "and sub.usu_codigo = "
					+ strCodeUsu
					+ " "
					+ "union select campo_n1 "
					+ "from paoyer.tcs_usuario "
					+ "where campo_n1 is not null "
					+ "and codigo = "
					+ strCodeUsu
					+ " "
					+ "union  select area.area_codigo "
					+ "from tcs_ben_subprograma_usuario subus, tcs_ben_subprograma sub, tcs_ben_area area "
					+ "where subus.SBPR_CODIGO = sub.CODIGO "
					+ "and sub.area_codigo = area.codigo "
					+ "and (sub.vigencia = 'A' OR sysdate between sub.DESDE_VIGENCIA and  sub.HASTA_VIGENCIA) "
					+ "and subus.USU_CODIGO = "
					+ strCodeUsu
					+ " ) "
					+ "order by a.NOMBRE ";
		}

		ArrayList secretarias = new ArrayList();
		AreaVO area = null;
		ResultSet rs = null;
		try {
			rs = cf.getRecordsRset(strSQL, null, conn);
			while (rs.next()) {
				area = new AreaVO();
				area.setCodearea(rs.getString("CODIGO"));
				area.setNombre(rs.getString("NOMBRE"));
				secretarias.add(area);
				area = null;
			}
		} finally {
			if (rs != null)
				rs.close();
		}

		return secretarias;
	}

	public Collection getAreasAuthorizedWithGestor(String strCodeUsu,
			String strGroupUser) throws SQLException {
		String strSQL = null;
		if (strGroupUser.equals("ADMINISTRADOR GENERAL")
				|| strGroupUser.equals("SUPER USUARIO")) {
			/*
			 * strSQL =
			 * "SELECT distinct area.CODIGO CODIGO, area.NOMBRE NOMBRE " +
			 * "FROM tcs_ben_area area, tcs_ben_programa prog, tcs_ben_subprograma sub, "
			 * + "tcs_ben_subp_atr_beneficiario sab " +
			 * "WHERE area.AREA_CODIGO is null AND  area.codigo = prog.area_codigo "
			 * +
			 * "AND prog.codigo = sub.prg_codigo AND sab.sbpr_codigo = sub.codigo "
			 * + "AND UPPER(TRIM(sab.nombre)) = 'GESTOR' " +
			 * "ORDER BY area.NOMBRE";
			 */
			strSQL = "SELECT distinct\n" + "sec.codigo, sec.nombre\n"
					+ "FROM\n" + "tcs_ben_area sec,\n" + "tcs_ben_area area,\n"
					+ "tcs_ben_subprograma sub,\n"
					+ "tcs_ben_subp_atr_beneficiario sab\n" + "WHERE\n"
					+ "sec.codigo = area.area_codigo\n"
					+ "AND area.codigo = sub.area_codigo\n"
					+ "AND sub.codigo = sab.sbpr_codigo\n"
					+ "AND upper(trim(sab.nombre)) = 'GESTOR'\n"
					+ "ORDER BY nombre\n";
		} else {
			/*
			 * strSQL = "SELECT area1.CODIGO CODIGO, area1.NOMBRE NOMBRE " +
			 * "from TCS_BEN_AREA area1 " + "where area1.CODIGO in (" +
			 * "select distinct area.AREA_CODIGO " +
			 * "from tcs_ben_subprograma_usuario subus, " +
			 * "tcs_ben_subprograma sub, tcs_ben_area area, " +
			 * "tcs_ben_subp_atr_beneficiario sab " +
			 * "where subus.SBPR_CODIGO = sub.CODIGO " +
			 * "and area.CODIGO = sub.AREA_CODIGO " + "and subus.USU_CODIGO = "
			 * + strCodeUsu + " " + "and sab.sbpr_codigo = sub.codigo " +
			 * "and UPPER(TRIM(sab.nombre)) = 'GESTOR')" +
			 * "order by area1.NOMBRE ";
			 */
			strSQL = "SELECT sec.codigo codigo, sec.nombre nombre\n"
					+ "FROM tcs_ben_area sec\n" + "WHERE sec.codigo IN (\n"
					+ "	SELECT area.area_codigo\n" + "		FROM\n"
					+ "			tcs_ben_area area,\n"
					+ "			tcs_ben_subp_atr_beneficiario sab,\n"
					+ "			tcs_ben_subprograma sub,\n"
					+ "			tcs_ben_subprograma_usuario subus\n" + "		WHERE\n"
					+ "			area.codigo = sub.area_codigo\n"
					+ "			AND upper(trim(sab.nombre)) = 'GESTOR'\n"
					+ "			AND sab.sbpr_codigo = sub.codigo\n"
					+ "			AND sub.codigo = subus.sbpr_codigo\n"
					+ "			AND subus.usu_codigo = " + strCodeUsu + "\n"
					+ "	UNION\n" + "	SELECT subus.campo_n1\n" + "		FROM\n"
					+ "			tcs_ben_subp_atr_beneficiario sab,\n"
					+ "			tcs_ben_subprograma sub,\n"
					+ "			tcs_ben_area area,\n"
					+ "			paoyer.tcs_usuario subus\n" + "		WHERE\n"
					+ "			upper(trim(sab.nombre)) = 'GESTOR'\n"
					+ "			AND sab.sbpr_codigo = sub.codigo\n"
					+ "			AND sub.area_codigo = area.codigo\n"
					+ "			AND area.area_codigo = subus.campo_n1\n"
					+ "			AND subus.codigo = " + strCodeUsu + "\n" + "	)\n"
					+ "order by sec.nombre";
		}

		ArrayList secretarias = new ArrayList();
		AreaVO area = null;
		ResultSet rs = null;
		try {
			rs = cf.getRecordsRset(strSQL, null, conn);
			while (rs.next()) {
				area = new AreaVO();
				area.setCodearea(rs.getString("CODIGO"));
				area.setNombre(rs.getString("NOMBRE"));
				secretarias.add(area);
				area = null;
			}
		} finally {
			if (rs != null)
				rs.close();
		}
		/*
		 * if (!strGroupUser.equals("ADMINISTRADOR GENERAL") &&
		 * !strGroupUser.equals("SUPER USUARIO")) { area =
		 * getAreaUser(strCodeUsu); if (area != null) secretarias.add(area); }
		 */
		return secretarias;
	}

	public AreaVO getAreaUser(String strCodeUser) throws SQLException {
		String strSQL = "SELECT area1.CODIGO CODIGO, area1.NOMBRE NOMBRE "
				+ "from TCS_BEN_AREA area1 " + "where area1.CODIGO = ("
				+ "select usu.CAMPO_N1 " + "from paoyer.tcs_usuario usu "
				+ "where usu.CODIGO = " + strCodeUser + ") "
				+ "order by area1.NOMBRE ";

		AreaVO area = null;
		ResultSet rs = null;
		try {
			rs = cf.getRecordsRset(strSQL, null, conn);
			if (rs.next()) {
				area = new AreaVO();
				area.setCodearea(rs.getString("CODIGO"));
				area.setNombre(rs.getString("NOMBRE"));
			}
		} finally {
			if (rs != null)
				rs.close();
		}

		return area;
	}

	public AreaVO getAreaByProg(String strCodeProg) throws SQLException {
		String strSQL = "select area.CODIGO CODEAREA, area.NOMBRE NAMEAREA "
				+ "from tcs_ben_programa prog, tcs_ben_area area "
				+ "where prog.AREA_CODIGO = area.CODIGO "
				+ "and prog.codigo = " + strCodeProg;

		AreaVO objAreaVO = new AreaVO();
		ResultSet rs = null;
		try {
			rs = cf.getRecordsRset(strSQL, null, conn);
			if (rs.next()) {
				objAreaVO.setNombre(rs.getString("NAMEAREA"));
				objAreaVO.setCode(new Long(rs.getString("CODEAREA")));
			}
		} finally {
			if (rs != null)
				rs.close();
		}

		return objAreaVO;
	}

	public Connection getConn() {
		return conn;
	}
}
